package com.hzzh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hzzh.util.ConnectionFactory;
import com.hzzh.vo.Employee;

public class EmployeeDAO {

	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	// 根据部门查询当前部门的员工
	public List<Employee> selectEmployeesByDept(int departmentid) {

		List<Employee> empList = new ArrayList<Employee>();
		Employee employee = null;
		conn = ConnectionFactory.getConnection();
		String sql = "select * from employee where departmentid=" + departmentid;
		try {

			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				employee = new Employee();
				employee.setEmployeeid(rs.getInt("employeeid"));
				employee.setEmployeename(rs.getString("employeename"));
				employee.setUsername(rs.getString("username"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setStatus(rs.getString("status"));
				employee.setDepartmentid(rs.getInt("departmentid"));
				employee.setPassword(rs.getString("password"));
				employee.setRole(rs.getString("role"));
				empList.add(employee);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}

		return empList;
	}

	// 登录
	public Employee SelectByNamePwd(String username, String password) throws ClassNotFoundException, SQLException {
		Employee e = new Employee();
		PreparedStatement ps = ConnectionFactory.getConnection()
				.prepareStatement("select * from employee where username=? and password=?");

		ps.setString(1, username);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();

		while (rs.next()) {
			e.setUsername(rs.getString(3));
			e.setPassword(rs.getString(8));
			e.setEmail(rs.getString(5));
			e.setEmployeeid(rs.getInt(1));
			e.setPhone(rs.getString(4));
			e.setStatus(rs.getString(6));
			e.setDepartmentid(rs.getInt(7));
			e.setRole(rs.getString(9));
			e.setEmployeename(rs.getString(2));
		}
		rs.close();
		ps.close();
		ConnectionFactory.getConnection().close();
		return e;
	}

	// 通过用户名查询，
	public Employee selectByUsername(String username) {
		conn = ConnectionFactory.getConnection();
		Employee employee = null;
		try {
			String sql = "select * from employee where username='" + username + "'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			if (rs.next() == true) {
				employee = new Employee();
				employee.setEmployeeid(rs.getInt("employeeid"));
				employee.setEmployeename(rs.getString("employeename"));
				employee.setUsername(rs.getString("username"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setStatus(rs.getString("status"));
				employee.setDepartmentid(rs.getInt("departmentid"));
				employee.setPassword(rs.getString("password"));
				employee.setRole(rs.getString("role"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return employee;
	}

	// 通过id查询，返回Employee对象
	public Employee selectById(int id) {
		conn = ConnectionFactory.getConnection();
		Employee employee = null;
		try {
			String sql = "select * from employee where employeeid=" + id;
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			if (rs.next() == true) {
				employee = new Employee();
				employee.setEmployeeid(rs.getInt("employeeid"));
				employee.setEmployeename(rs.getString("employeename"));
				employee.setUsername(rs.getString("username"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setStatus(rs.getString("status"));
				employee.setDepartmentid(rs.getInt("departmentid"));
				employee.setPassword(rs.getString("password"));
				employee.setRole(rs.getString("role"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return employee;
	}

	// 查询所有正在审核的员工信息
	public List<Employee> selectAllEmployee() {
		conn = ConnectionFactory.getConnection();
		List<Employee> employeeslist = new ArrayList<Employee>();
		Employee employee = null;
		try {
			String sql = "select * from employee where role='2' and status='0'";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
			while (rs.next()) {
				employee = new Employee();
				employee.setEmployeeid(rs.getInt("employeeid"));
				employee.setEmployeename(rs.getString("employeename"));
				employee.setUsername(rs.getString("username"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setStatus(rs.getString("status"));
				employee.setDepartmentid(rs.getInt("departmentid"));
				employee.setPassword(rs.getString("password"));
				employee.setRole(rs.getString("role"));
				employeeslist.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return employeeslist;
	}
	
	
	// 查询所有员工信息
		public ArrayList<Employee> selectAllEmployeesDAO() throws ClassNotFoundException, SQLException{
			ArrayList<Employee> al = new ArrayList<Employee>();
			PreparedStatement ps = ConnectionFactory.getConnection().prepareStatement("select * from employee where role ='2' and status = '0'");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Employee e1 = new Employee();
				e1.setEmployeeid(rs.getInt(1));
				e1.setEmployeename(rs.getString(2));
				e1.setUsername(rs.getString(3));
				e1.setPhone(rs.getString(4));
				e1.setEmail(rs.getString(5));
				e1.setStatus(rs.getString(6));
				e1.setDepartmentid(rs.getInt(7));
				e1.setPassword(rs.getString(8));
				e1.setRole(rs.getString(9));
				al.add(e1);
			}
			rs.close();
			ps.close();
			ConnectionFactory.getConnection().close();
			return al;
		}
		//修改状态
		public void updateStatusDAO(int employeeid,String status) throws ClassNotFoundException, SQLException{
			PreparedStatement ps = ConnectionFactory.getConnection().prepareStatement("update employee set status = ? where employeeid = ?");
			ps.setString(1,status);
			ps.setInt(2, employeeid);
			ps.executeUpdate();
			ps.close();
			ConnectionFactory.getConnection().close();
		}
		//搜索员工
		public ArrayList<Employee> selectEmployeesByNameStatusDAO(String employeename,String username,String status) throws ClassNotFoundException, SQLException{
			ArrayList<Employee> al = new ArrayList<Employee>();
			Employee e1 = null;
			PreparedStatement ps = null;
			String sql = null;
			String usernamesql,employeenamesql,statussql;
			
			if(employeename == null || employeename.equals("")){
				employeenamesql = "";
			}else{
				employeenamesql = "and employeename='"+employeename+"'";
			}
			
			if(username == null || username.equals("")){
				usernamesql = "";
			}else{
				usernamesql = "and username='"+username+"'";
			}
			
			if(status == null || status.equals("") || status.equals("3")){
				statussql = "";
			}else{
				statussql = "and status='"+status+"'";
			}
			sql = "select * from employee where role='2'"+usernamesql+employeenamesql+statussql;
			ps = ConnectionFactory.getConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				e1 = new Employee();
				e1.setEmployeeid(rs.getInt(1));
				e1.setEmployeename(rs.getString(2));
				e1.setUsername(rs.getString(3));
				e1.setPhone(rs.getString(4));
				e1.setEmail(rs.getString(5));
				e1.setStatus(rs.getString(6));
				e1.setDepartmentid(rs.getInt(7));
				e1.setPassword(rs.getString(8));
				e1.setRole(rs.getString(9));
				al.add(e1);
			}
			rs.close();
			ps.close();
			ConnectionFactory.getConnection().close();
			return al;
		}
		//分页
		public ArrayList<Employee> selectEmployeesOfOnePageDAO(String employeename,String username,String status,int pageSize,int pageNum) throws ClassNotFoundException, SQLException{
			ArrayList<Employee> al = new ArrayList<Employee>();
			Employee e1 = null;
			PreparedStatement ps = null;
			String sql = null;
			String usernamesql,employeenamesql,statussql;
			
			if(employeename == null || employeename.equals("")){
				employeenamesql = "";
			}else{
				employeenamesql = "and employeename='"+employeename+"'";
			}
			
			if(username == null || username.equals("")){
				usernamesql = "";
			}else{
				usernamesql = "and username='"+username+"'";
			}
			
			if(status == null || status.equals("") || status.equals("3")){
				statussql = "";
			}else{
				statussql = "and status='"+status+"'";
			}
			sql = "select * from(select e.*,rownum rn from employee e where rownum <= ? and role = '2'"+usernamesql+employeenamesql+statussql+") where rn > ?";
			//"select * from(select u.*,rownum rn from users u where rownum <= ?) where rn > ?"
			ps = ConnectionFactory.getConnection().prepareStatement(sql);
			ps.setInt(1, pageNum*pageSize);
			ps.setInt(2, (pageNum-1)*pageSize);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				e1 = new Employee();
				e1.setEmployeeid(rs.getInt(1));
				e1.setEmployeename(rs.getString(2));
				e1.setUsername(rs.getString(3));
				e1.setPhone(rs.getString(4));
				e1.setEmail(rs.getString(5));
				e1.setStatus(rs.getString(6));
				e1.setDepartmentid(rs.getInt(7));
				e1.setPassword(rs.getString(8));
				e1.setRole(rs.getString(9));
				al.add(e1);
			}
			rs.close();
			ps.close();
			ConnectionFactory.getConnection().close();
			return al;
		}

}
